package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import com.atsmart.app.model.TemplateInfo;
import com.atsmart.app.model.TemplateListInfo;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
/**
 * TODO 模板表
 */
public class TemplateSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addTemplateInfo(TemplateInfo at,int type,int task_id) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into template_info (tem_id,name,desc,type,task_id) values(?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1, at.getId());
			stm.bindString(2, at.getName()==null?"":at.getName());
			stm.bindString(3, at.getDesc()==null?"":at.getDesc());
			stm.bindLong(4,type);
			stm.bindLong(5,task_id);
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public TemplateListInfo getTemplateInfoByTaskId(int type,int task_id) {
		String sql ="";
	//	if(type==0){
		//	sql = " select _id,tem_id,name,desc from tem_info where type="+type+" limit "
		//			+ begin + "," + pagesize;	
	//	}else{
			sql = " select _id,tem_id,name,desc,type,task_id from template_info where task_id="+task_id;
	//	}
		 
		Cursor cursor = db.rawQuery(sql, null);
		TemplateInfo historie = null;
		List<TemplateInfo> nums = null;
		TemplateListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<TemplateInfo>();
				t = new TemplateListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new TemplateInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setDesc(cursor.getString(3));
					historie.setLocation_flag(cursor.getInt(4));
					historie.setUpdate_type(cursor.getInt(5));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setTemplates(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	public TemplateListInfo getTemplateInfo(int begin, int pagesize,int type,int task_id) {
		String sql ="";
	//	if(type==0){
			sql = " select _id,tem_id,name,desc from template_info where type="+type+" limit "
					+ begin + "," + pagesize;	
	//	}else{
	//		sql = " select _id,tem_id,name,desc from template_info where type="+type+" and tem_id in (select pattern_id from task_template_info where task_id ="+task_id+")"+" limit "
	//				+ begin + "," + pagesize;
	//	}
		 
		Cursor cursor = db.rawQuery(sql, null);
		TemplateInfo historie = null;
		List<TemplateInfo> nums = null;
		TemplateListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<TemplateInfo>();
				t = new TemplateListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new TemplateInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setDesc(cursor.getString(3));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setTemplates(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	
	
	public TemplateListInfo getTemplateInfoById(int tem_id,int type) {
		String sql ="";
		sql = " select _id,tem_id,name,desc from template_info where tem_id="+tem_id+" and type="+type;
		 
		Cursor cursor = db.rawQuery(sql, null);
		TemplateInfo historie = null;
		List<TemplateInfo> nums = null;
		TemplateListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<TemplateInfo>();
				t = new TemplateListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new TemplateInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setDesc(cursor.getString(3));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setTemplates(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	public int getCount(int type,int task_id) {
		String sql = " select _id,tem_id,name,desc from template_info where type="+type;
	//	if(type==1){
	//		 sql = " select _id,tem_id,name,desc from template_info where type="+type+" and tem_id in (select pattern_id from task_template_info where task_id ="+task_id+")";
	//	}
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}
	
	public int getCount(){
		String sql = "select * from template_info";
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if(null != cursor && cursor.moveToFirst()){
				return cursor.getCount();
			}
		} catch (Exception e) {
			// TODO: handle exceptiond
			return 0;
		}finally{
			cursor.close();
		}
		return 0;
	}

	public void deleteAttribute(int tem_id,int task_id) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from template_info where tem_id="+tem_id+" and task_id="+task_id);
	}
	public void deleteTem(int tem_id,int type) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from template_info where tem_id="+tem_id +" and type="+type);
	}
	public void delete() {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from template_info ");
	}
}
